ajDBRunSPToWorkbook function

Available since AlchemyJ v4.1.3

Description

The ajDBRunSPToWorkbook function executes a stored procedure and writes the result to a workbook. Please take note that to run this function from Excel, you would need to set up the Data Source Connection in ##ExternalResources.

Syntax

ajDBRunSPToWorkbook(target_workbook_path, start_address, stored_procedure_name, [parameter], [return_type],[transpose], [return_header], [data_source_id], [convert_to_text], [run_condition], [run_by_function_point_only])

Argument Name	Argument Type	Description
target_workbook_path (required)	String	The path of the Excel file to be written. The function will create an Excel file automatically if the specified file path does not exist. Ensure that the current user has privilege to add/update files in the specified path. Remark: The function only supports the target workbook in xlsx and xlsm format and it cannot not be the same as the current workbook.
start_address (required)	String	The top-left cell of the range to write in the target workbook in the [sheet]!cell_address format. For example, "sheet1!A2". The function will check the range size in data_range to determine the actual range in the target workbook.
stored_procedure_name (required)	Range / Array	The name of the stored procedure to execute.
parameter (optional)	Double	The range of cells to form the list of input and output parameters. Refer to the SQLParameters Snippet to see how to use this snippet to define the required fields.
return_type (optional)	Double	0 indicates the function will return a set of records (in cell array). 1 indicates the function will return the output parameters. 2 indicates to return the number of the affected records, or the number of records when stored procedure with SQL select statement. The default value is 0.
transpose (optional)	Boolean	If it equals TRUE, the return result will be transposed. This only affects the return that has multiple cells. The default value is FALSE.
return_header (optional)	Boolean	If it equals TRUE, the return result will include the column headers as the first row. This only applies when ‘return_type’ is 0 or 1. If it equals FALSE, the return result will contain data only. The default value is FALSE.
data_source_id (optional)	String	The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary".
convert_to_text (optional)	Boolean	If it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type. The default value is FALSE.
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: TRUE / #VALUE!

2) Return Type: Single Value

Example

Before using ajDBRunSPToWorkbook, the database connection must be set up in the Data Source Connection in ##ExternalResources worksheet.

Before running this function point, you need to configure the Data Source ID in ##RestEndpointGroup worksheet or ##JavaApiClass.

ajDBRunSPToWorkbook is a combined function of ajDBRunStoredProc and ajWriteWorkbook.

Examples use the following data.

Example 1 - Return Type is 0

A stored procedure returns records, the stored procedure as below.

DELIMITER $$

CREATE PROCEDURE `SP_PROC2`(cname varchar(50), clevel int)
BEGIN
    SELECT CUSTOMER_NAME,GENDER,CUSTOMER_LVL FROM tb_customer WHERE CUSTOMER_NAME = cname OR CUSTOMER_LVL > clevel;
END $$

DELIMITER ;

Click AlchemyJ ribbon Insert Snippet and select SQL Parameters . A table will be inserted.

We use the formula below to export the data to a specified path. If the specified path or file does not exist, it will create the folder and workbook . Otherwise, it will update the content to the existing one.

=ajDBRunSPToWorkbook(B1,B2,B3,B5:E8,,,B11,,,,B15)

The start address is sheet1!B2, so the content starts from this cell. The column headers were also shown since the return header is TRUE.

Example 2 - Return Type is 1

A stored procedure returns output parameter whose direction is inout or out, the stored procedure as below.

DELIMITER $$

CREATE PROCEDURE `SP_PROC3`(inout cname varchar(50), in new_level int, out output_clevel int)
BEGIN
    UPDATE tb_customer SET CUSTOMER_LVL = new_level WHERE CUSTOMER_NAME = cname;
    SET output_clevel = new_level;
    SET cname = concat(cname,'_U');
END $$

DELIMITER ;

In SQL Parameters table, the values are as below.

The return_type is 1, it returns the values of output parameter.

Example 3 - Return Type is 2

Export the data to workbook is a number of record which is selected from database, and the stored procedure is as same as example1.

The values of parameter are as below.

The return_type is 2, it returns a number of record which is selected from database.

Example 4 - Parameter Separators

The separator symbols is configured in %%AppConfig worksheet.

The value of parameter is as below, use the value of parameter-separator to separator parameter name, and use the value of parameter-type-separator to separator parameter name,value,type and direction.

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Target workbook path is not a xlsx file or a xlsm file.
Invalid start address, it should be in [sheet name]![cell address] format. For example, sheet1!A2.
Failed to write workbook.
Target workbook is current workbook.
DB connection error.
Invalid stored procedure name.
Invalid return type, it must be 0, 1, or 2.
Return header is TRUE, but return type is not 0 or 1.
For PostgreSQL, the return type is 0 or 2